package util;

import entity.CellEntity;
import entity.ExcelEntity;
import entity.SheetEntity;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelReader {
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";


    private static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }

        return workbook;
    }


    public static ExcelEntity readExcel(String fileName) {
        ExcelEntity result = new ExcelEntity();
        Workbook workbook = null;
        FileInputStream inputStream = null;

        try {

            File excelFile = new File(fileName);
            if (!excelFile.exists()) {
                return result;
            }

            inputStream = new FileInputStream(excelFile);
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
            workbook = getWorkbook(inputStream, fileType);

            result = parseExcel(workbook, fileName);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception ignored) { }
        }

        return result;
    }


    private static ExcelEntity parseExcel(Workbook workbook, String fileName) {
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        ExcelEntity resultData = new ExcelEntity(fileName);
        List<SheetEntity> sheetEntities = new ArrayList<>();

        workbook.sheetIterator().forEachRemaining(currentSheet -> {
            SheetEntity sheetEntity = new SheetEntity(currentSheet.getSheetName());
            List<CellEntity> cellEntityList = new ArrayList<>();

            currentSheet.rowIterator().forEachRemaining(currentRow -> {
                currentRow.cellIterator().forEachRemaining(currentCell -> {
                    CellEntity cellEntity = new CellEntity(
                            convertCellValueToString(currentCell, evaluator),
                            currentCell.getRowIndex(),
                            currentCell.getColumnIndex()
                    );
                    cellEntityList.add(cellEntity);
                });
            });

            sheetEntity.setCells(cellEntityList);
            sheetEntities.add(sheetEntity);
        });

        resultData.setSheets(sheetEntities);
        return resultData;
    }

    private static String convertCellValueToString(Cell cell, FormulaEvaluator evaluator) {
        if(cell==null){
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:   // number
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    returnValue = DateFormat.getDateInstance(DateFormat.SHORT).format(date);

                } else {
                    returnValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case STRING:    // string
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:   // boolean
                boolean booleanValue = cell.getBooleanCellValue();
                returnValue = String.valueOf(booleanValue);
                break;
            case BLANK:     // blank
                returnValue = "";
                break;
            case FORMULA:   // formula
                returnValue = getCellValue(evaluator.evaluate(cell));
                break;
            case ERROR:     // error
                break;
            default:
                break;
        }
        return returnValue;
    }

    private static String getCellValue(CellValue cell) {
        String cellValue = null;
        switch (cell.getCellType()) {
            case STRING:
                cellValue=cell.getStringValue();
                break;
            case NUMERIC:
                cellValue=String.valueOf(cell.getNumberValue());
                break;
            case FORMULA:
            default:
                break;
        }

        return cellValue;
    }
}
